/*********************************************************************************
 *      Copyright:  (C) 2022 Zhang Changxing<ZhangChangxingVIP@163.com>
 *                  All rights reserved.
 *
 *       Filename:  database.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(04/13/2022)
 *         Author:  Zhang Changxing <ZhangChangxingVIP@163.com>
 *      ChangeLog:  1, Release initial version on "04/13/2022 01:38:33 AM"
 *                 
 ********************************************************************************/

#include "database.h"

#define DBNAME "temperature"
#define TABLENAME "RPI"
sqlite3 *db;

/*---------------------------------------------------------------------------------------------------
 *   description:   Database initialization, open the database and create a table
 *  return value:   <0: failure,    =0:ok 
 *---------------------------------------------------------------------------------------------------*/
int  sqlite_init(void)
{
	char dbname_buf[64];
	char 	sql[100];
	int 	rc =-1;
	char 	*zErrMsg = 0;

	if((!TABLENAME) || (!DBNAME))
	{
		log_error("The %s() argument incorrect!\n", __func__);
		return -1;
	}

	sprintf(dbname_buf, "../database/%s.db", DBNAME);
	rc = sqlite3_open(dbname_buf, &db);
	if(rc)
	{
		log_error("Can't open database:%s\n", sqlite3_errmsg(db));
		return -2;
	}

	log_info("Opened database successfully\n");

	sprintf(sql, "CREATE TABLE if not exists %s(SN CHAR(10),DATIME CHAR(50),TEMPERATURE CHAR(10));", TABLENAME);
	rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);
	if (rc != SQLITE_OK)
	{
		log_error("Sqlite create table:%s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		unlink(dbname_buf);
		return -2;
	}
	
	log_debug("Table created successfully!\n");
	return 0;
}


/*---------------------------------------------------------------------------------------------------
 *   description:   Insert the data that failed to send into the table of the database
 *          args:   $pack_info:     Packet structure
 *  return value:   <0: failure,    =0:ok 
 *---------------------------------------------------------------------------------------------------*/
int db_data_insert(pack_info_t pack_info)
{
	char 	sql[512];
	int 	rc =-1;
	char 	*zErrMsg = 0;

	if(NULL == TABLENAME)
	{
		log_error("The %s() argument incorrect!\n", __func__);
		return -1;
	}

	sprintf(sql, "INSERT INTO %s VALUES ('%s', '%s', '%f');" ,\
	TABLENAME, pack_info.devid, pack_info.strtime, pack_info.temper);
	log_debug("data insert sql:%s\n", sql);

	rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);
   	if ( rc != SQLITE_OK )
	{
      	log_error("Sqlite_insert: %s\n", zErrMsg);
      	sqlite3_free(zErrMsg);
		return -2;
   	}

	log_info("Last data insert table successfully: %s, %s, %f\n" ,\
	pack_info.devid, pack_info.strtime, pack_info.temper);
	return 0;
}


/*---------------------------------------------------------------------------------------------------
 *   description:   Find the first data from table_name in the database and store it in the pack_info structure
 *          args:   $pack_info:     Packet structure
 *                  $table_name:    Database table name
 *  return value:   <0: failure,    =0:ok 
 *---------------------------------------------------------------------------------------------------*/
int select_first_data(pack_info_t *pack_info)
{
	char 	sql[100];
	int 	rc =-1;
	char 	*zErrMsg = 0;
	char 	**dbResult;
	int 	nRow = 0, nColumn = 0;

	if((NULL == pack_info)||(NULL == TABLENAME))
	{
		log_error("The %s() argument incorrect!\n", __func__);
		return -1;
	}

	memset(sql, 0, sizeof(sql));
	sprintf(sql, "select * from %s limit 1", TABLENAME);

	rc = sqlite3_get_table(db, sql, &dbResult, &nRow, &nColumn, &zErrMsg);
	if (rc != SQLITE_OK)
	{
		log_error("Execute sqlite_table_exist:%s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		return -2;
	}

	memset(pack_info, 0, sizeof(pack_info));
	strcpy(pack_info->devid, dbResult[3]);
	strcpy(pack_info->strtime, dbResult[4]);
	pack_info->temper = atof(dbResult[5]);
	log_debug("select_first_data,pack_info->devid:%s  pack_info->strtime:%s  pack_info->temper:%f\n", \
	pack_info->devid,pack_info->devid,pack_info->temper);

	return 0;
}


/*---------------------------------------------------------------------------------------------------
 *   description:   Delete the first data in the database
 *          args:   $table_name:    Database table name
 *  return value:   <0: failure,    =0:ok 
 *---------------------------------------------------------------------------------------------------*/
int db_data_delete(void)
{
	char 	sql[100];
	int 	rc =-1;
	char 	*zErrMsg = 0;

	if(NULL == TABLENAME)
	{
		log_error("The %s() argument incorrect!\n", __func__);
		return -1;
	}

	//sprintf(sql, "DELETE from %s limit 1;", TABLENAME);
	sprintf(sql, "delete from %s where rowid in (select rowid from %s limit 1);", TABLENAME, TABLENAME);
	rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);
   	if ( rc != SQLITE_OK )
   	{
      	log_error("Sqlite_delete error: %s\n", zErrMsg);
     	 sqlite3_free(zErrMsg);
		 return -2;
  	}

	log_info("Delete database first data successfully!\n");
	return 0;
}


/*---------------------------------------------------------------------------------------------------
 *   description:   Check whether the table is empty
 *          args:   $table_name:    Database table name
 *  return value:   <0: failure,    =0:ok 
 *---------------------------------------------------------------------------------------------------*/
int check_table_data(void)
{
	char 	sql[100];
	int 	rc =-1;
	char 	*zErrMsg = 0;
	char 	**dbResult;
	int 	nRow = 0, nColumn = 0;

	if(NULL == TABLENAME)
	{
		log_error("The %s() argument incorrect!\n", __func__);
		return -1;
	}	

	memset(sql, 0, sizeof(sql));
	//sprintf(sql, "select * from %s limit 1", TABLENAME);
	sprintf(sql, "select rowid from %s limit 1", TABLENAME);

	rc = sqlite3_get_table(db, sql, &dbResult, &nRow, &nColumn, &zErrMsg);
	if (rc != SQLITE_OK)
	{
		log_error("Execute sqlite_table_exist:%s\n", zErrMsg);
		sqlite3_free(zErrMsg);
		return -2;
	}
	log_debug("nRow:%d\n", nRow);
	return nRow;
}


/*---------------------------------------------------------------------------------------------------
 *   description:   Determine whether the database is still in use and shut down the database
 *  return value:   <0: failure,    =0:ok 
 *---------------------------------------------------------------------------------------------------*/
int close_database(void)
{
	int rc = sqlite3_close(db);

	while ( rc == SQLITE_BUSY)
	{
		// set rc to something that will exit the while loop
		rc = SQLITE_OK;
		sqlite3_stmt * stmt = sqlite3_next_stmt(db, NULL);

		if (stmt != NULL)
		{
			rc = sqlite3_finalize(stmt);
			if (rc == SQLITE_OK)
			{
				rc = sqlite3_close(db);
			}
		}
	}

	log_debug("Close database ucessfully!\n");
	return 0;
}